clear all 
set mem 14g
set more off 

 
* Creating dictionaries to check which individuals are in the sample per year
forvalues i= 2012/2015 {
cd "C:\Users\sfleit2\Documents\ReclassificationRisk_DoFiles"
use database_by_members_n_choiceset.dta, clear
drop if choice!=1
drop if year!=`i'
keep mbr_sys_id age gdr_cd paid_pharmacy net_pd_amt
sort mbr_sys_id
rename paid_pharmacy pharmacy_cost
rename net_pd_amt    total_cost
rename mbr_sys_id patient_id  
rename gdr_cd sex
save patient_year`i'.dta, replace
outsheet using patient_year`i'.csv, comma replace
 }
*

*****************************************************************************************
*****************************************************************************************
* Creating auxiliary databases by year and by claims file to construct year databases
*****************************************************************************************
*** For claims for years 2012 y 2013
forvalues j = 1/8 {
forvalues i= 2012/2013 {
cd "C:\PlanChoice\"
use mc_2015_medclaims`j'_8.dta, clear  

gen month = month(fst_srvc_dt)
drop if month>10

rename mbr_sys_id patient_id 
sort patient_id
cd "C:\Users\sfleit2\Documents\ReclassificationRisk_DoFiles"
drop if year!=`i' 
merge patient_id using patient_year`i'.dta
drop if _merge!=3
gen dx_version_1 = "9"
rename diag_1_cd dx_cd_1
keep patient_id dx_version_1 dx_cd_1 clm_aud_nbr 
save medical_claims`j'_year`i'_10months.dta, replace
outsheet patient_id dx_version_1 dx_cd_1 using medical_claims`j'_year`i'_10months.csv, comma replace 
}
}
****appending the aux databases for 2012-2013
* Creating the medical file (all diangnostics per individuals) 
forvalues i=2012/2013 {
use medical_claims1_year`i'_10months.dta, clear 
forvalues j = 2/8 {
append using medical_claims`j'_year`i'_10months.dta
}
order patient_id dx_version_1 dx_cd_1
sort patient_id dx_version_1 dx_cd_1
save medical_year`i'_10months.dta, replace 
outsheet using medical_year`i'_10months.csv, comma replace
}
*****************************************************************************************
*** For claims for year 2014
cd "C:\Users\sfleit2\Documents\ReclassificationRisk_DoFiles"
use MedClaims_MultCh2014.dta
gen year=2014
gen month = month(fst_srvc_dt)
drop if month>10
rename mbr_sys_id patient_id 
sort patient_id
drop if year!=2014 
merge patient_id using patient_year2014.dta
drop if _merge!=3
gen dx_version_1 = "9"
rename diag_1_cd dx_cd_1
keep patient_id dx_version_1 dx_cd_1 clm_aud_nbr 
save medical_claims_year2014_10months.dta, replace
order patient_id dx_version_1 dx_cd_1
sort patient_id dx_version_1 dx_cd_1
outsheet patient_id dx_version_1 dx_cd_1 using medical_year2014_10months.csv, comma replace 
*****************************************************************************************
*****************************************************************************************
*****************************************************************************************
*** For claims for year 2015
cd "C:\Users\sfleit2\Documents\ReclassificationRisk_DoFiles"
use MultCh_MedClaims_2015.dta
gen year=2015
gen month = month(fst_srvc_dt)
drop if month>10
rename mbr_sys_id patient_id 
sort patient_id
drop if year!=2015 
merge patient_id using patient_year2015.dta
drop if _merge!=3
gen dx_version_1 = "9"
rename diag_1_cd dx_cd_1
keep patient_id dx_version_1 dx_cd_1 clm_aud_nbr 
save medical_claims_year2015_10months.dta, replace
order patient_id dx_version_1 dx_cd_1
sort patient_id dx_version_1 dx_cd_1
outsheet patient_id dx_version_1 dx_cd_1 using medical_year2015_10months.csv, comma replace 
*****************************************************************************************
*****************************************************************************************





*****************************************************************************************
* End of medical claims 
*****************************************************************************************          
        *exit
*****************************************************************************************
* Begining of pharma claims
*****************************************************************************************




**************************************************************************
*********** PHARMA CLAIMS (BOTH IN PHARMA AND EMAIL ORDERS)
**************************************************************************
* Creating the pharma file (all rx records per individuals)
set more off 
forvalues i=2012/2013 {
cd "C:\PlanChoice\"
use mc_2015_rxclaims.dta, clear 
keep if year==`i'
gen month = month( fill_dt )

drop if month>10

gen day = day( fill_dt)
tostring month, replace
tostring year, replace
tostring day, replace 
local variables "month day"
foreach x of local variables {
replace `x'="01" if `x'=="1" 
replace `x'="02" if `x'=="2" 
replace `x'="03" if `x'=="3" 
replace `x'="04" if `x'=="4" 
replace `x'="05" if `x'=="5" 
replace `x'="06" if `x'=="6" 
replace `x'="07" if `x'=="7" 
replace `x'="08" if `x'=="8" 
replace `x'="09" if `x'=="9" 
}
egen rx_fill_date = concat(year month day), punct(-)
rename day_spl_cnt rx_days_supply 
rename mbr_sys_id patient_id
rename ndc rx_code
sort patient_id
cd "C:\Users\sfleit2\Documents\ReclassificationRisk_DoFiles"
merge patient_id using patient_year`i'.dta
drop if _merge!=3
gen rx_code_type = "N"
keep patient_id rx_fill_date rx_code rx_code_type rx_days_supply
sort patient_id rx_fill_date rx_code rx_code_type rx_days_supply
order patient_id rx_fill_date rx_code rx_code_type rx_days_supply
save pharma_year`i'_10months.dta, replace 
*outsheet using pharma_year`i'.csv, comma replace
}
* Creating the pharma file (all rx records per individuals)
set more off 
forvalues i=2012/2013 {
cd "C:\PlanChoice\"
use mc_2015_rxclaims_sol.dta, clear
keep if year==`i'
gen month = month( fill_dt )

drop if month>10


gen day = day( fill_dt)
tostring month, replace
tostring year, replace
tostring day, replace 
local variables "month day"
foreach x of local variables {
replace `x'="01" if `x'=="1" 
replace `x'="02" if `x'=="2" 
replace `x'="03" if `x'=="3" 
replace `x'="04" if `x'=="4" 
replace `x'="05" if `x'=="5" 
replace `x'="06" if `x'=="6" 
replace `x'="07" if `x'=="7" 
replace `x'="08" if `x'=="8" 
replace `x'="09" if `x'=="9" 
}
egen rx_fill_date = concat(year month day), punct(-)
rename day_spl_cnt rx_days_supply 
rename mbr_sys_id patient_id
rename ndc rx_code
sort patient_id
cd "C:\Users\sfleit2\Documents\ReclassificationRisk_DoFiles"
merge patient_id using patient_year`i'.dta
drop if _merge!=3
gen rx_code_type = "N"
keep patient_id rx_fill_date rx_code rx_code_type rx_days_supply
sort patient_id rx_fill_date rx_code rx_code_type rx_days_supply
order patient_id rx_fill_date rx_code rx_code_type rx_days_supply
save pharma2_year`i'_10months.dta, replace 
*outsheet using pharma2_year`i'.csv, comma replace
}
*** appending all pharmacy claims for years 2012-2013
forvalues i=2012/2013 {
use pharma2_year`i'_10months.dta, clear
append using pharma_year`i'_10months.dta
outsheet using pharma_year`i'_10months.csv, comma replace
}
******************************************************************
* Creating pharma for 2014
use MultCh_Pharm_2014.dta, clear 
gen year=2014
keep if year==2014
gen month = month( fill_dt )

drop if month>10

gen day = day( fill_dt)
tostring month, replace
tostring year, replace
tostring day, replace 
local variables "month day"
foreach x of local variables {
replace `x'="01" if `x'=="1" 
replace `x'="02" if `x'=="2" 
replace `x'="03" if `x'=="3" 
replace `x'="04" if `x'=="4" 
replace `x'="05" if `x'=="5" 
replace `x'="06" if `x'=="6" 
replace `x'="07" if `x'=="7" 
replace `x'="08" if `x'=="8" 
replace `x'="09" if `x'=="9" 
}
egen rx_fill_date = concat(year month day), punct(-)
rename day_spl_cnt rx_days_supply 
rename mbr_sys_id patient_id
rename ndc rx_code
sort patient_id
cd "C:\Users\sfleit2\Documents\ReclassificationRisk_DoFiles"
merge patient_id using patient_year2014.dta
drop if _merge!=3
gen rx_code_type = "N"
keep patient_id rx_fill_date rx_code rx_code_type rx_days_supply
sort patient_id rx_fill_date rx_code rx_code_type rx_days_supply
order patient_id rx_fill_date rx_code rx_code_type rx_days_supply
save pharma_year2014_10months.dta, replace 
outsheet using pharma_year2014_10months.csv, comma replace
*******************************************************************
*******************************************************************


******************************************************************
* Creating pharma for 2015
use MultCh_Pharm_2015.dta, clear 
gen year=2015
keep if year==2015
gen month = month( fill_dt )

drop if month>10

gen day = day( fill_dt)
tostring month, replace
tostring year, replace
tostring day, replace 
local variables "month day"
foreach x of local variables {
replace `x'="01" if `x'=="1" 
replace `x'="02" if `x'=="2" 
replace `x'="03" if `x'=="3" 
replace `x'="04" if `x'=="4" 
replace `x'="05" if `x'=="5" 
replace `x'="06" if `x'=="6" 
replace `x'="07" if `x'=="7" 
replace `x'="08" if `x'=="8" 
replace `x'="09" if `x'=="9" 
}
egen rx_fill_date = concat(year month day), punct(-)
rename day_spl_cnt rx_days_supply 
rename mbr_sys_id patient_id
rename ndc rx_code
sort patient_id
cd "C:\Users\sfleit2\Documents\ReclassificationRisk_DoFiles"
merge patient_id using patient_year2015.dta
drop if _merge!=3
gen rx_code_type = "N"
keep patient_id rx_fill_date rx_code rx_code_type rx_days_supply
sort patient_id rx_fill_date rx_code rx_code_type rx_days_supply
order patient_id rx_fill_date rx_code rx_code_type rx_days_supply
save pharma_year2015_10months.dta, replace 
outsheet using pharma_year2015_10months.csv, comma replace
*******************************************************************
*******************************************************************


exit 


/*


*******************************************************************
*******************************************************************
** IMPORTING RISK FROM HOPKINS SOFTWARE AND CREATE FILE TO MERGE
*******************************************************************
*******************************************************************
clear 
cd "C:\Users\sfleit2\Documents\ReclassificationRisk_DoFiles"
***********************************************
insheet using NEW_July2017_Output_Scores_2014_10months.csv, comma
keep patient_id age sex unscaled_acg_concurrent_risk rescaled_acg_concurrent_risk unscaled_concurrent_risk rescaled_concurrent_risk unscaled_total_cost_predicted_ri rescaled_total_cost_predicted_ri
tempfile year2014
sort patient_id
save `year2014'
use patient_year2014.dta, clear
merge patient_id using `year2014'
tempfile risk2014
sort patient_id
drop _merge
gen year=2014
save `risk2014'
***********************************************
clear
insheet using NEW_July2017_Output_Scores_2015_10months.csv, comma
keep patient_id age sex unscaled_acg_concurrent_risk rescaled_acg_concurrent_risk unscaled_concurrent_risk rescaled_concurrent_risk unscaled_total_cost_predicted_ri rescaled_total_cost_predicted_ri
tempfile year2015
sort patient_id
save `year2015'
use patient_year2015.dta, clear
merge patient_id using `year2015'
tempfile risk2015
sort patient_id
drop _merge
gen year=2015
save `risk2015'
***********************************************
clear
insheet using NEW_July2017_Output_Scores_2012_10months.csv, comma
keep patient_id age sex unscaled_acg_concurrent_risk rescaled_acg_concurrent_risk unscaled_concurrent_risk rescaled_concurrent_risk unscaled_total_cost_predicted_ri rescaled_total_cost_predicted_ri
tempfile year2012
sort patient_id
save `year2012'
use patient_year2012.dta, clear
merge patient_id using `year2012'
tempfile risk2012
sort patient_id
drop _merge
gen year=2012
save `risk2012'
***********************************************
clear 
insheet using NEW_July2017_Output_Scores_2013_10months.csv, comma
keep patient_id age sex unscaled_acg_concurrent_risk rescaled_acg_concurrent_risk unscaled_concurrent_risk rescaled_concurrent_risk unscaled_total_cost_predicted_ri rescaled_total_cost_predicted_ri
tempfile year2013
sort patient_id
save `year2013'
use patient_year2013.dta, clear
merge patient_id using `year2013'
drop _merge 
gen year=2013
************************
append using `risk2012'
append using `risk2014'
append using `risk2015'
rename unscaled_total_cost_predicted_ri acg_riskscore_10months
rename patient_id mbr_sys_id 
sort mbr_sys_id year
order mbr_sys_id year
rename unscaled_acg_concurrent_risk acg_concurrent_10months
keep mbr_sys_id year acg_riskscore_10months acg_concurrent_10months  
save NEW_riskscores_July2017_inc2015_10months.dta, replace 
*******************************************************************
*******************************************************************
